using System;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Mono.Data.SqliteClient;
using UnityEngine;
using System.Collections;
using System.Collections.Generic;

/// <summary>
/// All valid supported db types must be included in this enum.
/// </summary>
public enum SupportedDbType { SQLITE, SQL_SERVER }

/// <summary>
/// Utility class for common database actions and information.
/// </summary>
public class GameDatabase : ScriptableObject {

	private static readonly List<string> LOCAL_DB_FILE_EXTENSIONS = new List<string> { ".db", ".db3", ".sqlite" };
	
	private DbConnection currentDbConnection = null;
	public DbConnection CurrentDbConnection 
	{
		get { return this.currentDbConnection; }
	}	
	
	
	#region public static utility methods
	/// <summary>
	/// Creates a database connection based on the supplied connection string.
	/// If one already exists for this exact connection string the method returns without creating a new one,
	/// otherwise closes any open connection and creates a new one.
	/// </summary>
	/// <param name='connectionString'>
	/// A valid connection string for the db connection.
	/// </param>
	/// <param name="dbType">
	/// One of the supported database types.
	public void SetConnection(string connectionString, SupportedDbType dbType)
	{		
		// don't bother if the connection string supplied isn't minimally valid
		if (string.IsNullOrEmpty(connectionString)) {
			return;
		}
		
		// Has a connection already been set?
		if (currentDbConnection != null)
		{
			// is it the same one being requested...?
			if (currentDbConnection.ConnectionString == connectionString)
			{
				// ...yes, so just return
				return;
			}
			else
			{
				// ...no, so close the existing connection
				currentDbConnection.Close ();
			}
		}
				
		try {
			// create a new connection
			switch (dbType) {
				case SupportedDbType.SQLITE:
					currentDbConnection = new SqliteConnection(connectionString);
					break;
				case SupportedDbType.SQL_SERVER:
					currentDbConnection = new SqlConnection(connectionString);
					break;
				default:
				break;
			}
		} catch (System.Exception ex) {
			
			LogException(ex);			
			throw;
		}
		
		return;
	}

	/// <summary>
	/// Sets the default db connection, which is a sqlite connection based on any local database file found.
	/// </summary>
	public void SetDefaultConnection()
	{
		string connectionString = GetDefaultDbConnectionString();
		SetConnection(connectionString, SupportedDbType.SQLITE);
	}
		
	/// <summary>
	/// Gets the apportiate DbDataAdaptor for the current connection.
	/// </summary>
	/// <returns>
	/// A db data adaptor.
	/// </returns>
	/// <exception cref='InvalidOperationException'>
	/// Thrown if this method is called before establishing a database connection.
	/// </exception>
	public DbDataAdapter GetDbDataAdaptor()
	{
		if (currentDbConnection == null)
		{
			throw new InvalidOperationException("A SetConnection method must be called first before attempting to acquire a data adapter.");
		}
		
		DbDataAdapter adapter = null;
		Type connectionType = currentDbConnection.GetType();
		if (connectionType == typeof(SqlConnection)) 
		{
			adapter = new SqlDataAdapter();
		}
		else if (connectionType == typeof(SqliteConnection))
		{
			adapter = new SqliteDataAdapter();
		}
		
		return adapter;
	}
	
	#endregion
	
	#region private helper methods
	
	private string GetDefaultDbConnectionString()
	{
		string localDBFilename = FindLocalDBFile(Application.dataPath);
		if (string.IsNullOrEmpty(localDBFilename))
		{
			return "";
		}
		
		return ConnectionStringFromLocalDBFilename(localDBFilename);
	}
	
	private string ConnectionStringFromLocalDBFilename (string localDBFilename)
	{
		if (string.IsNullOrEmpty(localDBFilename))
		{
			return "";
		}
		
		return "URI=file:" + localDBFilename;
	}	

	private void LogException(System.Exception ex)
	{
		StringBuilder sb = new StringBuilder();
		sb.AppendLine("The following error has occurred:");
		while (ex != null) 
		{
			sb.AppendLine(ex.Message);
			ex = ex.InnerException;
		}
	}
	
	/// <summary>
	/// Finds the first local file, if any, that "looks like" a database file.
	/// </summary>
	/// <returns>
	/// The full pathname of a possible local DB file.
	/// </returns>
	/// <param name='searchDirectory'>
	/// Search directory.
	/// </param>
	/// 
	private string FindLocalDBFile(string searchDirectory) {		
		DirectoryInfo di = new DirectoryInfo(searchDirectory);		
		FileInfo[] allFiles = di.GetFiles("*", SearchOption.AllDirectories);
		foreach (FileInfo fi in allFiles)
		{
			if (LOCAL_DB_FILE_EXTENSIONS.Contains(fi.Extension.ToLower()))
			{
				return fi.FullName;
			}			
		}
		
		// if we get here, no file was found
		return null;
	}
	
	#endregion
	
}


